﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Web;
using ChessWebService.DTO;


namespace ChessWebService.Models
{
    public class TaiKhoanDAO:DataProvider
    {
        public List<TaiKhoan> DanhSachTaiKhoan()
        {
            connect();
            string query = "select * " +
                            "from TaiKhoan ";
            adapter = new SqlDataAdapter(query, connection);
            DataSet dataset = new DataSet();
            adapter.Fill(dataset);
            DataTable dataTable = dataset.Tables[0];
            int n = dataTable.Rows.Count;
            List<TaiKhoan> listSV = new List<TaiKhoan>();
            for (int i = 0; i < n; i++)
            {
                object sv = GetDataFromDataRow(dataTable, i);
                listSV.Add((TaiKhoan)sv);
            }
            return null;
        }

        public List<TaiKhoan> LayDanhSachBan(int idTaiKhoan)
        {
            connect();
            string query = "select TaiKhoan.* from TaiKhoan,Friend where (TaiKhoan.Id=Friend.TaiKhoan02 and Friend.TaiKhoan01=" + idTaiKhoan + ") or (TaiKhoan.Id=Friend.TaiKhoan01 and Friend.TaiKhoan02=" + idTaiKhoan + ")";
            adapter = new SqlDataAdapter(query, connection);
            DataSet dataset = new DataSet();
            adapter.Fill(dataset);
            DataTable dataTable = dataset.Tables[0];
            int n = dataTable.Rows.Count;
            List<TaiKhoan> listFriend = new List<TaiKhoan>();
            for (int i = 0; i < n; i++)
            {
                object sv = GetDataFromDataRow(dataTable, i);
                listFriend.Add((TaiKhoan)sv);
            }
            return listFriend;
        }

        protected override object GetDataFromDataRow(DataTable dt, int i)
        {
            TaiKhoan tk = new TaiKhoan();

            tk.Id = Convert.ToInt32(dt.Rows[i]["Id"].ToString());
            tk.Email = dt.Rows[i]["Email"].ToString();
            tk.UserName = dt.Rows[i]["UserName"].ToString();
            if (!(dt.Rows[i]["HinhAnh"] is System.DBNull))
            {
                tk.HinhAnh = (byte[])dt.Rows[i]["HinhAnh"];                
            }                       
            tk.HoTen = dt.Rows[i]["HoTen"].ToString();            
            if (!(dt.Rows[i]["NgaySinh"] is System.DBNull))
            {
                string temp = dt.Rows[i]["NgaySinh"].ToString();
                tk.NgaySinh = Convert.ToDateTime(temp);
            }            
            return tk;            
        }

        public TaiKhoan KiemTraTaiKhoan(TaiKhoan taiKhoan)
        {
            try
            {
                connect();
                command = this.connection.CreateCommand();
                string query = "select * " +
                                "from TaiKhoan where UserName=N'" + taiKhoan.UserName + "' and password='" + taiKhoan.Password + "'";

                adapter = new SqlDataAdapter(query, connection);
                DataSet dataset = new DataSet();
                adapter.Fill(dataset);
                DataTable dataTable = dataset.Tables[0];
                int n = dataTable.Rows.Count;
                List<TaiKhoan> listSV = new List<TaiKhoan>();
                if (n > 0)
                {
                    object sv = GetDataFromDataRow(dataTable, 0);
                    return (TaiKhoan)sv;
                }
                else
                {
                    return null;
                }                
            }
            catch (Exception ex)
            {
                return null;
            }       
        }

        public TaiKhoan KiemTraTaiKhoan(String UserName)
        {
            try
            {
                connect();
                command = this.connection.CreateCommand();
                string query = "select * " +
                                "from TaiKhoan where UserName=N'"+UserName+"'";

                adapter = new SqlDataAdapter(query, connection);
                DataSet dataset = new DataSet();
                adapter.Fill(dataset);
                DataTable dataTable = dataset.Tables[0];
                int n = dataTable.Rows.Count;
                List<TaiKhoan> listSV = new List<TaiKhoan>();
                if (n > 0)
                {
                    object sv = GetDataFromDataRow(dataTable, 0);
                    return (TaiKhoan)sv;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                return null;
            }  
        }



        public bool ThemTaiKhoan(TaiKhoan taiKhoan)
        {
            try
            {
                connect();
                command = this.connection.CreateCommand();
                string query;
                SqlParameter parameter;

                if (taiKhoan.HinhAnh != null)
                {
                    query = "insert into TAIKHOAN(UserName,Password,Email,HoTen,NgaySinh,DiaChi,DienThoai,HinhAnh) values(@UserName,@Password,@Email,@HoTen,@NgaySinh,@DiaChi,@DienThoai,@HinhAnh)";
                    command.CommandText = query;
                    parameter = command.Parameters.Add("@HinhAnh", SqlDbType.Image);
                    parameter.Value = taiKhoan.HinhAnh;
                }
                else
                {
                    query = "insert into TAIKHOAN(UserName,Password,Email,HoTen,NgaySinh,DiaChi,DienThoai) values(@UserName,@Password,@Email,@HoTen,@NgaySinh,@DiaChi,@DienThoai)";
                    command.CommandText = query;
                }

                parameter = command.Parameters.Add("@UserName", SqlDbType.NVarChar);
                parameter.Value = taiKhoan.UserName;

                parameter = command.Parameters.Add("@Password", SqlDbType.NVarChar);
                parameter.Value = taiKhoan.Password;

                parameter = command.Parameters.Add("@Email", SqlDbType.NVarChar);
                parameter.Value = taiKhoan.Email;

                parameter = command.Parameters.Add("@HoTen", SqlDbType.NVarChar);
                parameter.Value = taiKhoan.HoTen;

                try
                {
                    parameter = command.Parameters.Add("@NgaySinh", SqlDbType.DateTime);
                    parameter.Value = Convert.ToDateTime(taiKhoan.NgaySinh);
                }
                catch (Exception ex)
                { 
                }

                parameter = command.Parameters.Add("@DiaChi", SqlDbType.NVarChar);
                parameter.Value = taiKhoan.DiaChi;

                parameter = command.Parameters.Add("@DienThoai", SqlDbType.NVarChar);
                parameter.Value = taiKhoan.DienThoai;

                this.command.ExecuteNonQuery();
                return true;
            }
            catch {
                return false;
            }
            
        }

        public void XoaTaiKhoan(int idTaiKhoan)
        {
            connect();
            string query = "delete from TaiKhoan where id=" + idTaiKhoan;
            command = connection.CreateCommand();
            command.CommandText = query;
            command.ExecuteNonQuery();
        }

        public void CapNhatTaiKhoan(TaiKhoan taiKhoan)
        {

            connect();
            command = this.connection.CreateCommand();
            SqlParameter parameter;

            if (taiKhoan.HinhAnh != null)
            {
                //insert into TAIKHOAN(UserName,Password,Email,HoTen,NgaySinh) values(@UserName,@Password,@Email,@HoTen,@NgaySinh)";
                string query = "update TaiKhoan set HoTen=@HoTen,Password=@Password,Email=@Email,NgaySinh=@NgaySinh,HinhAnh=@HinhAnh,DiaChi=@DiaChi,DienThoai=@DienThoai where Id=" + taiKhoan.Id;
                command.CommandText = query;
                parameter = command.Parameters.Add("@HinhAnh", SqlDbType.Image);
                parameter.Value = taiKhoan.HinhAnh;
            }
            else
            {
                string query = "update SINHVIEN set HoTen=@HoTen,Password=@Password,Email=@Email,NgaySinh=@NgaySinh where Id=" + taiKhoan.Id;
                command.CommandText = query;
            }


            parameter = command.Parameters.Add("@HoTen", SqlDbType.NVarChar);
            parameter.Value = taiKhoan.HoTen;

            parameter = command.Parameters.Add("@Password", SqlDbType.NVarChar);
            parameter.Value = taiKhoan.Password;

            parameter = command.Parameters.Add("@Email", SqlDbType.NVarChar);
            parameter.Value = taiKhoan.Email;

            try
            {
                parameter = command.Parameters.Add("@NgaySinh", SqlDbType.DateTime);
                parameter.Value = Convert.ToDateTime(taiKhoan.NgaySinh);
            }
            catch (Exception ex)
            {
            }


            parameter = command.Parameters.Add("@DiaChi", SqlDbType.NVarChar);
            parameter.Value = taiKhoan.DiaChi;

            parameter = command.Parameters.Add("@DienThoai", SqlDbType.NVarChar);
            parameter.Value = taiKhoan.DienThoai;

            this.command.ExecuteNonQuery();
        }


        public TaiKhoan KiemTraTaiKhoanOpenID(string OpenID)
        {
            try
            {
                connect();
                command = this.connection.CreateCommand();
                string query = "select * from taikhoan where openid='"+OpenID+"'";

                adapter = new SqlDataAdapter(query, connection);
                DataSet dataset = new DataSet();
                adapter.Fill(dataset);
                DataTable dataTable = dataset.Tables[0];
                int n = dataTable.Rows.Count;
                List<TaiKhoan> listSV = new List<TaiKhoan>();
                if (n > 0)
                {
                    object sv = GetDataFromDataRow(dataTable, 0);
                    return (TaiKhoan)sv;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                return null;
            }       
        }

        public bool ThemTaiKhoanOpenID(TaiKhoan taiKhoan)
        {
            try
            {
                connect();
                command = this.connection.CreateCommand();
                string query;
                SqlParameter parameter;

                query = "insert into TAIKHOAN(UserName,HoTen,OpenID) values(@UserName,@HoTen,@OpenID)";
                command.CommandText = query;

                parameter = command.Parameters.Add("@UserName", SqlDbType.NVarChar);
                parameter.Value = taiKhoan.UserName;

                parameter = command.Parameters.Add("@HoTen", SqlDbType.NVarChar);
                parameter.Value = taiKhoan.HoTen;

                parameter = command.Parameters.Add("@OpenID", SqlDbType.NVarChar);
                parameter.Value = taiKhoan.OpenID;

                this.command.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public TaiKhoan KiemTraTaiKhoanFacebookOAuth(string username, string mail)
        {
            try
            {
                connect();
                command = this.connection.CreateCommand();
                string query = "select * from taikhoan where IsOAuth=1 and UserName='"+username+"' and Email='"+mail+"'";

                adapter = new SqlDataAdapter(query, connection);
                DataSet dataset = new DataSet();
                adapter.Fill(dataset);
                DataTable dataTable = dataset.Tables[0];
                int n = dataTable.Rows.Count;
                List<TaiKhoan> listSV = new List<TaiKhoan>();
                if (n > 0)
                {
                    object sv = GetDataFromDataRow(dataTable, 0);
                    return (TaiKhoan)sv;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                return null;
            }       
        }

        public bool ThemTaiKhoanFacebookOAuth(TaiKhoan taiKhoan)
        {
            try
            {
                connect();
                command = this.connection.CreateCommand();
                string query;
                SqlParameter parameter;

                query = "insert into TAIKHOAN(UserName,HoTen,Email,DiaChi,IsOAuth) values(@UserName,@HoTen,@Email,@DiaChi,1)";
                command.CommandText = query;

                parameter = command.Parameters.Add("@UserName", SqlDbType.NVarChar);
                parameter.Value = taiKhoan.UserName;

                parameter = command.Parameters.Add("@HoTen", SqlDbType.NVarChar);
                parameter.Value = taiKhoan.HoTen;

                parameter = command.Parameters.Add("@Email", SqlDbType.NVarChar);
                parameter.Value = taiKhoan.Email;

                parameter = command.Parameters.Add("@DiaChi", SqlDbType.NVarChar);
                parameter.Value = taiKhoan.DiaChi;


                this.command.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }
        }
    }
}